package com.yaozheng.vocationaltraining.db;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.yaozheng.vocationaltraining.activity.BaseActivity;
import com.yaozheng.vocationaltraining.utils.JsonUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * 试卷答案缓存
 *
 * @author xiaoqing
 */
public class PaperAnswerCacheDao {
    /**
     * 表名字
     */
    public static final String TABLE_NAME = "paper_answer_cache";

    /**
     * 模块内容
     */
    public static final String PAPER_ID = "paperId";
    /**
     * 内容
     */
    public static final String ANSWERS_CONTENT = "answers_content";
    /**
     * 结束日期
     */
    public static final String END_DATE = "end_date";
    /**
     * 状态
     */
    public static final String STATUS = "status";

    /**
     * 创建表sql
     */
    public static final String CREATE_TABLE_SQL = "create table if not exists "
            + TABLE_NAME + " (" + PAPER_ID + " integer  PRIMARY KEY, "
            + ANSWERS_CONTENT + " TEXT," + END_DATE + " real ," + STATUS
            + " integer );";

    protected DbOpenHelper dbHelper;

    public PaperAnswerCacheDao(BaseActivity baseActivity) {
        dbHelper = DbOpenHelper.getInstance(baseActivity);
    }

    protected PaperAnswerCacheDao() {
        super();
    }

    /**
     * 保存
     *
     * @param paperId
     * @param answersContent
     * @param endDate
     * @throws Exception
     */
    public synchronized void save(int paperId, String answersContent,
                                  long endDate, int status) throws Exception {
        SQLiteDatabase db = null;
        try {
            db = dbHelper.getWritableDatabase();
            if (db.isOpen()) {
                ContentValues values = new ContentValues();
                values.put(PAPER_ID, paperId);
                values.put(ANSWERS_CONTENT, answersContent);
                values.put(END_DATE, endDate);
                values.put(STATUS, status);
                db.replace(TABLE_NAME, null, values);
            }
        } finally {
            if (db != null && db.isOpen()) {
                db.close();
                db = null;

            }
        }
    }

    /**
     * 删除 答案 或超过有效期的也删除
     *
     * @param paperId
     * @throws Exception
     */
    public synchronized void delete(String paperId) throws Exception {
        SQLiteDatabase db = null;
        try {
            db = dbHelper.getWritableDatabase();
            if (db.isOpen()) {
                db.delete(TABLE_NAME, PAPER_ID + " = ? or " + END_DATE
                                + "<datetime('now','localtime')",
                        new String[]{paperId});
            }
        } finally {
            if (db != null && db.isOpen()) {
                db.close();
                db = null;

            }
        }
    }

    /**
     * 删除 答案 或超过有效期的也删除
     *
     * @param paperId
     * @throws Exception
     */
    public synchronized void delete(int paperId) throws Exception {
        delete(String.valueOf(paperId));

    }

    /**
     * 删除有效时间数据
     */
    public synchronized void delete() throws Exception {
        SQLiteDatabase db = null;
        try {
            db = dbHelper.getWritableDatabase();
            if (db.isOpen()) {
                db.delete(TABLE_NAME, END_DATE + "<?", new String[]{String
                        .valueOf(System.currentTimeMillis())});
            }
        } finally {
            if (db != null && db.isOpen()) {
                db.close();
                db = null;

            }
        }
    }

    /**
     * 得到一项
     *
     * @param paperId
     * @return
     */
    public synchronized String get(String paperId) {
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = dbHelper.getReadableDatabase();
            if (db.isOpen()) {
                String sql = "select " + ANSWERS_CONTENT + " from "
                        + TABLE_NAME + " where " + PAPER_ID + "=" + paperId;
                cursor = db.rawQuery(sql, null);
                if (cursor.moveToNext()) {
                    return cursor.getString(cursor
                            .getColumnIndex(ANSWERS_CONTENT));
                }
            }
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
            if (db != null && db.isOpen()) {
                db.close();
                db = null;

            }
        }
        return null;
    }

    /**
     * 得到题目
     *
     * @param paperId
     * @return
     */
    public synchronized JSONObject getPaperQuestionJsonObject(String paperId) {

        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = dbHelper.getReadableDatabase();
            if (db.isOpen()) {
                String sql = "select " + ANSWERS_CONTENT + "," + END_DATE + " from "
                        + TABLE_NAME + " where " + PAPER_ID + "=" + paperId;
                cursor = db.rawQuery(sql, null);
                if (cursor.moveToNext()) {
                    JSONObject returnJsonObject = new JSONObject();
                    returnJsonObject.put("paperQuestions", JsonUtils
                            .getJsonObject(cursor.getString(cursor
                                    .getColumnIndex(ANSWERS_CONTENT))));
                    returnJsonObject.put("endTime", cursor.getLong(cursor
                            .getColumnIndex(END_DATE)));
                    return returnJsonObject;
                }
            }
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
            if (db != null && db.isOpen()) {
                db.close();
                db = null;

            }
        }
        return null;
    }

    /**
     * 根据状态取题目
     *
     * @param status 1:每日一测  2:随堂考试 4:大师之路 5:极限挑战  6:闯关练习
     * @return 0 数据 1 结束时间
     */
    public synchronized List findQuestionsByStatus(int status) {
        SQLiteDatabase db = null;
        Cursor cursor = null;
        List<String> returnObject = null;
        try {
            db = dbHelper.getReadableDatabase();
            if (db.isOpen()) {
                String sql = "select " + ANSWERS_CONTENT + "," + END_DATE + " from "
                        + TABLE_NAME + " where " + STATUS + "=" + status + " and " + END_DATE + ">" + System.currentTimeMillis();
                cursor = db.rawQuery(sql, null);

                if (cursor.moveToNext()) {
                    returnObject = new ArrayList<String>();
                    returnObject.add(cursor.getString(cursor
                            .getColumnIndex(ANSWERS_CONTENT)));
                    returnObject.add(String.valueOf(cursor.getLong(cursor
                            .getColumnIndex(END_DATE))));
                }
            }
        } finally {
            if (cursor != null) {
                cursor.close();
                cursor = null;
            }
            if (db != null && db.isOpen()) {
                db.close();
                db = null;

            }
        }
        return returnObject;
    }
}
